package edu.bw.dao.impl;

import edu.bw.dao.UserDao;
import edu.bw.dto.SelectUserByConditionForm;
import edu.bw.pojo.User;
import edu.bw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class UserDaoImpl implements UserDao {
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    /**
     * SELECT id
     * FROM bw_zzy_users
     * WHERE username = 'zhangsan'
     * AND `password`= HEX(AES_ENCRYPT('zhangsan','zhangsan'))
     *
     * param user
     * @return
     */
    @Override
    public Integer userLogin(User user) {
        try {
            return qr.query("SELECT id FROM bw_zzy_users WHERE username = ?" +
                            "AND `password`= HEX(AES_ENCRYPT(?,?))", new ScalarHandler<Integer>(),
                    user.getUsername(),user.getPassword(),user.getUsername());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectUserByCondition(SelectUserByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder("SELECT " +
                "  DISTINCT u.id, " +
                "  u.`name`, u.sex, " +
                "  u.tel, " +
                "  u.email, " +
                "  u.hiredate, " +
                "  ( SELECT GROUP_CONCAT( role_name SEPARATOR ', ' ) FROM bw_zzy_role WHERE JSON_CONTAINS( u.role, CONVERT ( id, JSON ) ) ) AS roles,  " +
                "  d.dept_name as dept, " +
                "  u.root, " +
                "  u.`status` " +
                "FROM " +
                "  bw_zzy_users u " +
                "  LEFT JOIN bw_zzy_role r ON JSON_CONTAINS( u.role, CONVERT ( r.id, JSON ) ) " +
                "  LEFT JOIN bw_zzy_dept d ON u.dept_id = d.id " +
                "  WHERE 1 = 1 ");
        List<Object> params = new ArrayList();

        if (selectCondition.getName() != null && !selectCondition.getName().isEmpty()) {
            sql.append("AND u.name LIKE ? ");
            params.add("%" + selectCondition.getName() + "%");
        }
        if (selectCondition.getSex() != null && !selectCondition.getSex().isEmpty()) {
            sql.append("AND u.sex = ? ");
            params.add(selectCondition.getSex());
        }
        if (selectCondition.getRole() != null && !selectCondition.getRole().isEmpty()) {
            sql.append("AND r.role_name = ? ");
            params.add(selectCondition.getRole());
        }
        if (selectCondition.getDeptId() != null) {
            sql.append("AND d.id = ? ");
            params.add(selectCondition.getDeptId());
        }
        if (selectCondition.getStatus() != null) {
            sql.append("AND u.status = ? ");
            params.add(selectCondition.getStatus());
        }

        sql.append(" LIMIT ?,?");
        params.add((selectCondition.getPage() - 1) * selectCondition.getLength());
        params.add(selectCondition.getLength());

        try {
            return qr.query(sql.toString(), new MapListHandler(), params.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Long selectUserByConditionCount(SelectUserByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder("  SELECT COUNT(DISTINCT u.id) " +
                "  FROM bw_zzy_users u " +
                "  LEFT JOIN bw_zzy_role r ON JSON_CONTAINS( u.role, CONVERT ( r.id, JSON ) )  " +
                "  LEFT JOIN bw_zzy_dept d ON u.dept_id = d.id " +
                "  WHERE 1 = 1 ");

        List<Object> params = new ArrayList();

        if (selectCondition.getName() != null && !selectCondition.getName().isEmpty()) {
            sql.append("AND u.name LIKE ? ");
            params.add("%" + selectCondition.getName() + "%");
        }
        if (selectCondition.getSex() != null && !selectCondition.getSex().isEmpty()) {
            sql.append("AND u.sex = ? ");
            params.add(selectCondition.getSex());
        }
        if (selectCondition.getRole() != null && !selectCondition.getRole().isEmpty()) {
            sql.append("AND r.role_name = ? ");
            params.add(selectCondition.getRole());
        }
        if (selectCondition.getDeptId() != null) {
            sql.append("AND d.id = ? ");
            params.add(selectCondition.getDeptId());
        }
        if (selectCondition.getStatus() != null) {
            sql.append("AND u.status = ? ");
            params.add(selectCondition.getStatus());
        }

        try {
            return qr.query(sql.toString(), new ScalarHandler<>(), params.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * INSERT INTO bw_zzy_users
     * SET
     * username = ? ,
     * `password` = HEX(AES_ENCRYPT(?,?)) ,
     * dept_id = ?,
     * open_id = ?,
     * `name` = ?,
     * ....
     * @param user
     * @return
     */
    @Override
    public Integer insert(User user) {
        StringBuilder sql = new StringBuilder(" INSERT INTO bw_zzy_users SET ");
        List<Object> insertParams = new ArrayList<>();

        //if (!StringUtils.isNullOrEmpty(user.getUsername())) {
        //    sql.append(" username=? ");
        //    insertParams.add(user.getUsername());
        //}
        //if (!StringUtils.isNullOrEmpty(user.getPassword())) {
        //    sql.append(" `password` = HEX(AES_ENCRYPT(?,?)) ");
        //    insertParams.add(user.getPassword());
        //    insertParams.add(user.getUsername());
        //}
        //if (user.getDeptId() != null) {
        //    sql.append(" dept_id = ? ");
        //    insertParams.add(user.getDeptId());
        //}
        //if (user.getOpenId() != null) {
        //    sql.append(" open_id = ? ");
        //    insertParams.add(user.getOpenId());
        //}
        //.....................

        //利用反射来拼接sql
        try {
            for (Field declaredField : user.getClass().getDeclaredFields()) {
                declaredField.setAccessible(true);
                if (declaredField.get(user) != null && !declaredField.get(user).equals("")) {
                    if (declaredField.getName().equals("password")) {
                        sql.append("`password` = HEX(AES_ENCRYPT(?,?)), ");
                        Field usernameField = user.getClass().getDeclaredField("username");
                        usernameField.setAccessible(true);

                        insertParams.add(declaredField.get(user));
                        insertParams.add(usernameField.get(user));
                    } else if (declaredField.getName().equals("deptId")) {
                        sql.append("`dept_id` = ?, ");
                        insertParams.add(declaredField.get(user));
                    } else if (declaredField.getName().equals("openId")) {
                        sql.append("`open_id` = ?, ");
                        insertParams.add(declaredField.get(user));
                    } else {
                        //属性名和数据库的列名相同
                        sql.append(declaredField.getName()+" = ?, ");
                        insertParams.add(declaredField.get(user));
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        try {
            return qr.update(sql.toString().replaceAll(",\\s*$", ""), insertParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectById(Integer userId) {
        try {
            return qr.query("SELECT id,username,CONVERT(AES_DECRYPT(UNHEX(`password`),username),CHAR) AS password" +
                    ",`name`,sex,tel,email,hiredate,role,dept_id AS deptId,`status` " +
                    "FROM bw_zzy_users " +
                    "WHERE id = ? ", new MapListHandler(), userId);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer update(User user) {
        StringBuilder sql = new StringBuilder(" UPDATE bw_zzy_users SET ");
        List<Object> updateParams = new ArrayList<>();

        try {
            for (Field declaredField : user.getClass().getDeclaredFields()) {
                declaredField.setAccessible(true);
                if (declaredField.get(user) != null && !declaredField.get(user).equals("")) {
                    if (declaredField.getName().equals("password")) {
                        sql.append("`password` = HEX(AES_ENCRYPT(?,?)), ");
                        Field usernameField = user.getClass().getDeclaredField("username");

                        usernameField.setAccessible(true);
                        updateParams.add(declaredField.get(user));
                        updateParams.add(usernameField.get(user));
                    }else if (declaredField.getName().equals("deptId")) {
                        sql.append(" dept_id = ?, ");
                        updateParams.add(declaredField.get(user));
                    }else if(declaredField.getName().equals("openId")) {
                        sql.append(" open_id  = ?, ");
                        updateParams.add(declaredField.get(user));
                    }else {
                        sql.append(declaredField.getName() + " = ? , ");
                        updateParams.add(declaredField.get(user));
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        sql.append(" WHERE id = ? ");
        updateParams.add(user.getId());
        try {
            return qr.update(sql.toString().replaceAll(",\\s*WHERE", " WHERE"), updateParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer deleteUserByIds(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_users WHERE id IN ( ");
        /*List<Object> deleteParams = new ArrayList<>();
        for (Integer id : ids) {
            sql.append(" ?,  ");
            deleteParams.add(id);
        }

        sql.append(" ) ");*/

        sql.append(ids.stream().map(id -> "?").collect(Collectors.joining(", "))).append(" )");

        try {
            return qr.update(sql.toString(), ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

